﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public class D_BGM_ProjectExpense : IDAL_BGM_ProjectExpense
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();

        public IList<M_BGM_ProjectExpense> GetFull(Nullable<int> bgm_proj_seq
            , Nullable<int> bgm_source_seq, Nullable<int> bgm_exp_grp_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_ProjectExpense> infoList = new List<M_BGM_ProjectExpense>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_proj_seq", bgm_proj_seq);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(3, "@bgm_exp_grp_seq", bgm_exp_grp_seq);

                strSQL = "SELECT recexp.bgm_proj_exp_seq, recexp.bgm_proj_seq, recexp.descr"
                    + ", recexp.total_amount, recexp.unit_price, recexp.unit_qty, recexp.remark"
                    + ", recexp.bgm_source_seq, recexp.bgm_exp_seq"

                    + ", coalesce(recexp.unit_price, 0) * coalesce(recexp.unit_qty, 0) AS amount"
                    + ", source.bgm_source_name, exp.bgm_exp_name"
                    + ", exp.bgm_exp_grp_seq, expgrp.bgm_exp_grp_name"
                    + ", recexp.record_status"
                    + " FROM bgm_project_expense recexp"
                    + " LEFT JOIN bgm_mas_source source ON recexp.bgm_source_seq = source.bgm_source_seq"
                    + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
                    + " LEFT JOIN bgm_mas_expense_group expgrp ON exp.bgm_exp_grp_seq = expgrp.bgm_exp_grp_seq"
                    + " WHERE 0=0"
                    + (bgm_proj_seq != null ? " AND recexp.bgm_proj_seq = @bgm_proj_seq" : "")
                    + (bgm_source_seq != null ? " AND recexp.bgm_source_seq = @bgm_source_seq" : "")
                    + (bgm_exp_grp_seq != null ? " AND exp.bgm_exp_grp_seq = @bgm_exp_grp_seq" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_ProjectExpense info = new M_BGM_ProjectExpense();
                        info.bgm_proj_exp_seq = Convert.ToInt32(drc[i]["bgm_proj_exp_seq"]);
                        info.bgm_proj_seq = cn.ConvertNullInt(drc[i]["bgm_proj_seq"]);
                        info.descr = drc[i]["descr"].ToString();
                        info.total_amount = cn.ConvertNullDouble(drc[i]["total_amount"]);
                        info.unit_price = cn.ConvertNullDouble(drc[i]["unit_price"]);
                        info.unit_qty = cn.ConvertNullInt(drc[i]["unit_qty"]);
                        info.remark = drc[i]["remark"].ToString();
                        info.bgm_source_seq = cn.ConvertNullInt(drc[i]["bgm_source_seq"]);
                        info.bgm_exp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_seq"]);

                        info.amount = cn.ConvertNullDouble(drc[i]["amount"]);
                        info.m_bgm_mas_source.bgm_source_name = drc[i]["bgm_source_name"].ToString();
                        info.m_bgm_mas_expense.bgm_exp_name = drc[i]["bgm_exp_name"].ToString();
                        info.m_bgm_mas_expense.bgm_exp_grp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_grp_seq"]);
                        info.m_bgm_mas_expensegroup.bgm_exp_grp_name = drc[i]["bgm_exp_grp_name"].ToString();

                        info.record_status = drc[i]["record_status"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_BGM_ProjectExpense> GetList(Nullable<int> bgm_proj_seq
            , Nullable<int> bgm_source_seq, Nullable<int> bgm_exp_grp_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_BGM_ProjectExpense> infoList = new List<M_BGM_ProjectExpense>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_proj_seq", bgm_proj_seq);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(3, "@bgm_exp_grp_seq", bgm_exp_grp_seq);

                strSQL = "SELECT recexp.bgm_proj_exp_seq, exp.bgm_exp_name"
                    + " FROM bgm_project_expense recexp"
                    + " LEFT JOIN bgm_mas_source source ON recexp.bgm_source_seq = source.bgm_source_seq"
                    + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
                    + " LEFT JOIN bgm_mas_expense_group expgrp ON exp.bgm_exp_grp_seq = expgrp.bgm_exp_grp_seq"
                    + " WHERE 0=0"
                    + (bgm_proj_seq != null ? " AND recexp.bgm_proj_seq = @bgm_proj_seq" : "")
                    + (bgm_source_seq != null ? " AND recexp.bgm_source_seq = @bgm_source_seq" : "")
                    + (bgm_exp_grp_seq != null ? " AND exp.bgm_exp_grp_seq = @bgm_exp_grp_seq" : "");

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_BGM_ProjectExpense info = new M_BGM_ProjectExpense();
                        info.bgm_proj_exp_seq = Convert.ToInt32(drc[i]["bgm_proj_exp_seq"]);
                        info.m_bgm_mas_expense.bgm_exp_name = drc[i]["bgm_exp_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_BGM_ProjectExpense GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_BGM_ProjectExpense info = new M_BGM_ProjectExpense();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT recexp.bgm_proj_exp_seq, recexp.bgm_proj_seq, recexp.descr"
                    + ", recexp.total_amount, recexp.unit_price, recexp.unit_qty, recexp.remark"
                    + ", recexp.bgm_source_seq, recexp.bgm_exp_seq, recexp.icm_res_item_seq"

                    + ", coalesce(recexp.price, 0) * coalesce(recexp.qty, 0) AS amount"
                    + ", source.bgm_source_name, exp.bgm_exp_name"
                    + ", exp.bgm_exp_grp_seq, expgrp.bgm_exp_grp_name, resi.icm_res_item_name"
                    + " FROM bgm_project_expense recexp"
                    + " LEFT JOIN bgm_mas_source source ON recexp.bgm_source_seq = source.bgm_source_seq"
                    + " LEFT JOIN bgm_mas_expense exp ON recexp.bgm_exp_seq = exp.bgm_exp_seq"
                    + " LEFT JOIN bgm_mas_expense_group expgrp ON exp.bgm_exp_grp_seq = expgrp.bgm_exp_grp_seq"
                    + " LEFT JOIN icm_mas_resource_item resi ON recexp.icm_res_item_seq = resi.icm_res_item_seq"
                    + " WHERE recexp.bgm_proj_exp_seq = @seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.bgm_proj_exp_seq = Convert.ToInt32(drc[0]["bgm_proj_exp_seq"]);
                    info.bgm_proj_seq = cn.ConvertNullInt(drc[0]["bgm_proj_seq"]);
                    info.descr = drc[0]["descr"].ToString();
                    info.total_amount = cn.ConvertNullDouble(drc[0]["total_amount"]);
                    info.unit_price = cn.ConvertNullDouble(drc[0]["unit_price"]);
                    info.unit_qty = cn.ConvertNullInt(drc[0]["unit_qty"]);
                    info.remark = drc[0]["remarks"].ToString();
                    info.bgm_source_seq = cn.ConvertNullInt(drc[0]["bgm_source_seq"]);
                    info.bgm_exp_seq = cn.ConvertNullInt(drc[0]["bgm_exp_seq"]);
                    info.icm_res_item_seq = cn.ConvertNullInt(drc[0]["icm_res_item_seq"]);
                    info.icm_res_item_name = drc[0]["icm_res_item_name"].ToString();

                    info.amount = cn.ConvertNullDouble(drc[0]["amount"]);
                    info.m_bgm_mas_source.bgm_source_name = drc[0]["bgm_source_name"].ToString();
                    info.m_bgm_mas_expense.bgm_exp_name = drc[0]["bgm_exp_name"].ToString();
                    info.m_bgm_mas_expense.bgm_exp_grp_seq = cn.ConvertNullInt(drc[0]["bgm_exp_grp_seq"]);
                    info.m_bgm_mas_expensegroup.bgm_exp_grp_name = drc[0]["bgm_exp_grp_name"].ToString();

                    info.record_status = drc[0]["record_status"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public int InsertProjectExpense(string bgm_proj_seq, string bgm_source_seq, string bgm_exp_seq, string total_amount
            , string descr, Nullable<int> icm_res_item_seq, Nullable<int> unit_qty, Nullable<decimal> unit_price
            , Nullable<decimal> mtef1, Nullable<decimal> mtef2, Nullable<decimal> mtef3, string created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(13);
                dbManager.AddParameters(0, "@bgm_proj_seq", bgm_proj_seq);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_exp_seq", bgm_exp_seq);
                dbManager.AddParameters(3, "@total_amount", total_amount);
                dbManager.AddParameters(4, "@descr", descr);

                dbManager.AddParameters(5, "@icm_res_item_seq", icm_res_item_seq);
                dbManager.AddParameters(6, "@unit_qty", unit_qty);
                dbManager.AddParameters(7, "@unit_price", unit_price);
                dbManager.AddParameters(8, "@mtef1", mtef1);
                dbManager.AddParameters(9, "@mtef2", mtef2);
                dbManager.AddParameters(10, "@mtef3", mtef3);
                dbManager.AddParameters(11, "@created_by", created_by);
                dbManager.AddParameters(12, "@created_when", DateTime.Now);

                string strsql = "Insert into bgm_project_expense ("
                                            + "bgm_proj_seq,"
                                            + "bgm_source_seq,"
                                            + "bgm_exp_seq,"
                                            + "total_amount,"
                                            + "descr,"
                                            + "icm_res_item_seq,"
                                            + "unit_qty,"
                                            + "unit_price,"
                                            + "mtef1,"
                                            + "mtef2,"
                                            + "mtef3,"
                                            + "created_by,"
                                            + "created_when)"
                                            + " values("
                                            + "@bgm_proj_seq,"
                                            + "@bgm_source_seq,"
                                            + "@bgm_exp_seq,"
                                            + "@total_amount,"
                                            + "@descr,"
                                            + "@icm_res_item_seq,"
                                            + "@unit_qty,"
                                            + "@unit_price,"
                                            + "@mtef1,"
                                            + "@mtef2,"
                                            + "@mtef3,"
                                            + "@created_by,"
                                            + "@created_when);"

                    + " SELECT scope_identity();";

                string strSeq = dbManager.ExecuteDataSet(CommandType.Text, strsql).Tables[0].Rows[0][0].ToString();
                return Convert.ToInt32(strSeq);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
